<?php

namespace app\admin\service;

use app\admin\controller\Excel;
use app\admin\model\User;
use app\common\BaseHelper;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use app\admin\model\Department;
use think\Db;

/**
 * 信访用户
 */
class Personnel02
{

    /**
     * 下载Excel模板
     */
    public static function downloadTemplate($request){
        $xlsTitle = 'Templete';
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);
        $spreadsheet->getActiveSheet()->setCellValue('A1', '信访用户基本信息');
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $worksheet = $spreadsheet->getActiveSheet();

        $index0 = 2;
        $cellArray0 = array(
            'A' => '姓名',
            'B' => '性别(1=男|2=女)',
            'C' => '身份证',
            'D' => '手机号',
            'E' => '所在省',
            'F' => '所在市',
            'G' => '所在区',
            'H' => '详细地址',
            'I' => '信访次数',
        );
        foreach ($cellArray0 as $key0 => $cell0) {
            $spreadsheet->getActiveSheet()->setCellValue($key0 . $index0, $cell0);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setWidth(10);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setAutoSize(true);
        }

        $cellArray = ['A', 'B', 'C', 'D', 'E', 'F', 'G'];
        $spreadsheet->getActiveSheet()->mergeCells('A4:J4');  //合并单元格
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $worksheet->getStyle('A4')->applyFromArray($styleArray);  //水平居中

        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'member' . DS . 'templete' . DS . date('Y-m-d');
        $fileName = $filePath . '/member_templete_' . date('y-m-d-H-i-s');

        //检查文件或目录是否存在
        if(!file_exists($filePath)){
            mkdir($filePath, 0777, true);
        }
        $writer = new Xlsx($spreadsheet);
        //$writer->save($fileName . '.xls'); // or .xlsx

        $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
        $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

        $writer->save('php://output');
        exit;
    }

    /**
     * 导入数据
     */
    public static function import(){
        $file = request()->file('csv_file');
        $model = new \app\admin\model\User();

        Db::startTrans();
        try {
            $filePath = ROOT_PATH . 'public' . DS . 'upload';
            $info = $file->move($filePath);
            $fileName = $info->getSaveName();
            //$fileName = 'user_' . date('y-m-d-H-i-s') . '.' . substr($file,strripos($file,'.')+1);
            //move_uploaded_file($file, iconv("UTF-8","gb2312", $filePath . DS . $fileName));
            $fileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filePath . DS . $fileName);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($fileType);
            // 设置编码
            if(strtolower($fileType) == 'csv'){
                $reader->setInputEncoding('CP936'); // 只对csv文件有效
            }
            $spreadsheet = $reader->load($filePath . DS . $fileName);

            $sheetData = $spreadsheet->getActiveSheet()->toArray();
            if(count($sheetData) <= 1){
                return json(['status' => 0, 'msg' => '未找到文件内容，请填写数据。']);
            }

            foreach ($sheetData as $key => $val) {
                if($key == 0){
                    continue;
                }
                if(count($val) < 9){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '文件内容不正确，请按照模板来填写内容。']);
                }

                // 必填字段 验证
                if(!$val[1] || !$val[3] || !$val[4]){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '姓名、身份证、手机号等是必填字段']);
                }
                //账号检测
                $User = User::where('p_phone',$val[4])->find();

                if($User){
                    continue;
                }
                // 姓名
                $name = $val[0];
                // 性别
                $sex = in_array($val[1], [1, 2]) ? $val[1] : 1;
                //身份证
                $identityNumber = $val[2];
                //手机号
                $p_phone = $val[3];
                /*//所在地
                $item = explode('-',$val[4]);
                $province  = $item[0];
                $city      = $item[1];
                $county    = $item[2];*/
                $province = $val[4];
                $city = $val[5];
                $county = $val[6];
                // 详细地址
                $address = $val[7];
                //信访次数
                $number = $val[8];
                $data = [
                    'id'                    => BaseHelper::getUUID(),
                    'p_name'                => $name,
                    'sex'                   => $sex,
                    'identityNumber'        => $identityNumber,
                    'p_phone'               => $p_phone,
                    'province'              => $province,
                    'city'                  => $city,
                    'county'                => $county,
                    'address'               => $address,
                    'number'                => $number,
                    'username'              => $p_phone,
                    'type'                  => 2
                ];

                $result = $model->isUpdate(false)->save($data);

                if($result === false){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '导入失败！']);
                }
            }
            @unlink($filePath . DS . $fileName);

            Db::commit();
            return json(['status' => 1, 'msg' => '导入成功！']);
        } catch (\Exception $e){
            Db::rollback();
            $msg = $e->getMessage();
            //$msg = '导入失败，请严格按照模板来填写内容。';
            return json(['status' => 0, 'msg' => $msg]);
        }

        return json(['status' => 0, 'msg' => $msg]);
    }

    /**
     * 导出数据
     */
    public static function export($request){
        $param = $request->param();

        $where = [
            'status' => 1,
            'type'   => 2
        ];

        if(!empty($param['identityNumber'])){
            $where['identityNumber'] = ['like', '%'.$param['identityNumber'].'%'];
        }
        if(!empty($param['p_phone'])){
            $where['p_phone'] = ['like', '%'.$param['p_phone'].'%'];
        }

        $model = model('User');
        $data = $model->where($where)->order('createDate', 'asc')->select();
        if(count($data) <= 0){
            return [
                'code' => 2,
                'msg' => '没有数据！'
            ];
        }

        foreach ($data as $key => $val) {
            // 性别
            switch ($val['sex']) {
                case 1: $val['sex'] = '男'; break;
                case 2: $val['sex'] = '女'; break;
                default : $val['sex'] = '男'; break;
            }

            $val['location'] = $val['province'].'-'.$val['city'].'-'.$val['county'];
        }
        $cellName = array(
            'A' => ['id', '编号'],
            'B' => ['p_name', '姓名'],
            'C' => ['sex', '性别'],
            'D' => ['identityNumber', '身份证'],
            'E' => ['p_phone', '手机号'],
            'F' => ['location', '所在地'],
            'G' => ['address', '详细地址'],
            'H' => ['number', '信访次数'],
        );

        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'visituser' . DS . date('Y-m-d');
        $fileName = $filePath . '/visituser_' . date('y-m-d-H-i-s');

        //检查文件或目录是否存在
        if(!file_exists($filePath)){
            mkdir($filePath, 0777, true);
        }

        $excel = new Excel();
        $excel->exportExcel('工作人员', $data, $cellName, $filePath, $fileName);

        return [
            'code' => 1,
            'msg' => '导出成功！'
        ];
    }

}